package com.leandro.tweet;

import java.io.BufferedInputStream;
import java.io.InputStream;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.SimpleTimeZone;

import org.apache.http.util.ByteArrayBuffer;
import twitter4j.Status;
import twitter4j.User;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

public class DbHelper extends SQLiteOpenHelper
{
	public static final String TAG = TweetProjectApplication.APP_NAME;
	public static final int DB_VAZIO = 1;

	public Context context;

	public String tableStatuses;
	public String colunaIdStatuses;
	public String colunaCreatedAtStatuses;
	public String colunaUserIdStatuses;
	public String colunaTextStatuses;

	public String tableUser;
	public String colunaUserIdUser;
	public String colunaNomeUser;
	public String colunaImagemUser;
	public String colunaUrlImagemUser;

	public DbHelper(Context context, String name, int version)
	{
		super(context, name, null, version);

		this.context = context;
		tableStatuses = "statuses";
		colunaIdStatuses = "_id"; // id tem q ser assim nao sei bem porque
		colunaCreatedAtStatuses = "createdAt";
		colunaUserIdStatuses = "userId";
		colunaTextStatuses = "text";

		tableUser = "users";
		colunaUserIdUser = "_id";
		colunaNomeUser = "nome";
		colunaImagemUser = "imagem";
		colunaUrlImagemUser = "url";

	}

	@Override
	public void onCreate(SQLiteDatabase db)
	{
		// String sql = String.format("create table %s (%s int primary key, %s INT, %s TEXT, %s DATE)", table, colunaId, colunaCreatedAt, colunaUser, colunaText);

		String sql = "create table " + tableStatuses;
		sql += " (" + colunaIdStatuses + " int primary key, ";
		sql += colunaCreatedAtStatuses + " TEXT, ";
		sql += colunaUserIdStatuses + " INT, ";
		sql += colunaTextStatuses + " TEXT)";

		db.execSQL(sql);

		sql = "create table " + tableUser;
		sql += " (" + colunaUserIdUser + " int primary key, ";
		sql += colunaNomeUser + " TEXT, ";
		sql += colunaUrlImagemUser + " TEXT, ";
		sql += colunaImagemUser + " BLOB)";

		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		// TODO: fazer a logica de atualizacao do banco
		db.execSQL("drop table " + tableStatuses);
		db.execSQL("drop table " + tableUser);
		onCreate(db);

	}
	
	public synchronized void limparBanco(){
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("drop table " + tableStatuses);
		db.execSQL("drop table " + tableUser);
		onCreate(db);
		db.close();
	}

	/**
	 * 
	 * @param statuses
	 *            a ser salva, caso hajo alguma status novo
	 * @return returna a quantidade de status novos
	 */
	public synchronized void saveStatuses(List<Status> statuses)
	{

		// Open Database
		SQLiteDatabase db = getWritableDatabase();

		for (Status status : statuses)
		{

			ContentValues valuesStatuses = new ContentValues();
			ContentValues valuesUser = null;

			/**
			 * checar se ja nao tenho a imagem checar se a imagem mudou tb seria uma boa ideia
			 */

			if (!imgJaConstaNoBanco(status.getUser().getId(), db))
			{
				ByteArrayBuffer byteArrayBuffer = getImageAsByteArrayFromWeb(status.getUser().getId());
				valuesUser = new ContentValues();
				valuesUser.put(colunaUserIdUser, status.getUser().getId());
				valuesUser.put(colunaNomeUser, status.getUser().getName());
				valuesUser.put(colunaImagemUser, byteArrayBuffer.toByteArray());
				valuesUser.put(colunaUrlImagemUser, status.getUser().getProfileImageURL().toString());
			}

			valuesStatuses.put(colunaIdStatuses, status.getId());
			valuesStatuses.put(colunaUserIdStatuses, status.getUser().getId());
			valuesStatuses.put(colunaTextStatuses, status.getText());

			/**
			 * formatando a data
			 */
			SimpleDateFormat dateFormat = new SimpleDateFormat();
			dateFormat.setTimeZone(new SimpleTimeZone(0, "GMT"));
			dateFormat.applyPattern("dd MMM yyyy HH:mm:ss z");
			String createdAtS = dateFormat.format(status.getCreatedAt());
			/**
			 * fim de formatando a data
			 */
			
			Log.d(TAG, "Tweets: " + status.getText());

			valuesStatuses.put(colunaCreatedAtStatuses, createdAtS);

			// Insert into database
			try
			{
				db.insertOrThrow(tableStatuses, null, valuesStatuses);
				if (valuesUser != null)
				{
					db.insertOrThrow(tableUser, null, valuesUser);
				}

			}
			catch (SQLException e)
			{
				/*
				 * so sao pegos os tweets novos entao em teoria nao deve entrar nesse catch
				 */
				Log.e(TAG, "nao salvou no bd: " + e.getMessage());

			}
		}

		db.close();
	}

	/**
	 * 
	 * @return retorna a ultima id, caso a tabela esteja vazia return -1;
	 */
	public synchronized long getLatestId()
	{
		long latestId;
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor;
		int index;
		String[] colunas =
		{ colunaIdStatuses };

		cursor = db.query(tableStatuses, colunas, null, null, null, null, colunaIdStatuses + " DESC LIMIT 1");
		index = cursor.getColumnIndex(colunaIdStatuses);
		if (!cursor.moveToFirst())
		{
			return DB_VAZIO;
		}
		latestId = cursor.getLong(index);
		cursor.close();
		db.close();
		return latestId;
	}

	public synchronized Cursor getCursor()
	{
		// Open Database
		SQLiteDatabase db = getReadableDatabase();

		String[] colunas =
		{ colunaCreatedAtStatuses, tableStatuses + "." + colunaIdStatuses, colunaNomeUser, colunaTextStatuses, colunaUserIdStatuses, tableUser + "." + colunaUserIdUser, colunaImagemUser };
		String whereLeft = colunaUserIdStatuses + " = " + tableUser + "." + colunaUserIdUser;
		// String[] whereRight = {tableUser+"."+colunaUserIdUser};
		String table = tableStatuses + ", " + tableUser;
		return db.query(table, colunas, whereLeft, null, null, null, tableStatuses + "." + colunaIdStatuses + " DESC");
	}

	private ByteArrayBuffer getImageAsByteArrayFromWeb(Long userId)
	{
		InputStream is;
		URL url = null;
		User user;
		TwitterHelper twitterHelper;

		twitterHelper = ((TweetProjectApplication) context.getApplicationContext()).getTwitterHelper();
		user = twitterHelper.getUser(userId);

		url = user.getProfileImageURL();

		try
		{
			url = user.getProfileImageURL();

			is = (InputStream) url.getContent();

			/*
			 * convertendo para um array de bytes para ser armazenado no bd
			 */
			BufferedInputStream bufferedInputStream = new BufferedInputStream(is, 128);
			ByteArrayBuffer bayteArrayBuffer = new ByteArrayBuffer(128);

			int current = 0;
			while ((current = bufferedInputStream.read()) != -1)
			{
				bayteArrayBuffer.append((byte) current);
			}

			return bayteArrayBuffer;
		}
		catch (Exception e)
		{
			Log.d(TAG, TimeLineActivity.class.getSimpleName() + " funcao getImageFromWeb " + e.getMessage());
			return null;
		}
	}

	private synchronized boolean imgJaConstaNoBanco(long id, SQLiteDatabase db)
	{

		Cursor cursor;

		/**
		 * ================================================================================================ esses comentarios sao importantes
		 * 
		 */
		// URL url = null;
		// User user;
		String[] colunas =
		{ colunaUserIdUser };
		// TwitterHelper twitterHelper;

		// twitterHelper = ((TweetProjectApplication) context.getApplicationContext()).getTwitterHelper();
		// user = twitterHelper.getUser(id);
		// url = user.getProfileImageURL();

		// cursor = db.query(tableUser, colunas, colunaUserIdUser + " = " + id + " and " + colunaUrlImagemUser + " = " + "\"" + url.toString() + "\"", null, null, null, null);
		cursor = db.query(tableUser, colunas, colunaUserIdUser + " = " + id, null, null, null, null);
		if (cursor.moveToFirst())
		{
			Log.d(TAG, "img ja consta no banco");
			return true;

		}

		/**
		 * Ola amiga gambi, vou apagar o usuario com a img desatualizada (ou inexistente)
		 */

		db.delete(tableUser, colunaUserIdUser + " = " + id, null);

		return false;
	}

	public synchronized long getMaxId()
	{
		long maxId;
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor;
		int index;
		String[] colunas =
		{ colunaIdStatuses };

		cursor = db.query(tableStatuses, colunas, null, null, null, null, colunaIdStatuses + " ASC LIMIT 1");
		index = cursor.getColumnIndex(colunaIdStatuses);
		if (!cursor.moveToFirst())
		{
			return DB_VAZIO;
		}
		maxId = cursor.getLong(index);
		cursor.close();
		db.close();
		return maxId;
	}

}
